// 统计分析-统计图的数据-模型表
const { query, db } = require('../database/db')
class AnalysisModel {
    // 一. 概况统计
    // 1.成绩分布图数据
    static score_chart(class_id, program_id, exam_id) {
        let sql = `
            SELECT
                SUM(CASE WHEN exam_results.score_all BETWEEN 0 AND 60 THEN 1 ELSE 0 END) AS '0-60',
                SUM(CASE WHEN exam_results.score_all BETWEEN 61 AND 70 THEN 1 ELSE 0 END) AS '61-70',
                SUM(CASE WHEN exam_results.score_all BETWEEN 71 AND 80 THEN 1 ELSE 0 END) AS '71-80',
                SUM(CASE WHEN exam_results.score_all BETWEEN 81 AND 90 THEN 1 ELSE 0 END) AS '81-90',
                SUM(CASE WHEN exam_results.score_all BETWEEN 91 AND 100 THEN 1 ELSE 0 END) AS '91-100'
            FROM exam_results 
            JOIN students ON exam_results.student_id = students.id 
            JOIN classes ON students.class_id = classes.id 
            JOIN programs ON classes.program_id = programs.id 
            JOIN exams ON exam_results.exam_id = exams.id
        `;
        // 初始化参数数组
        let params = [];

        // 如果有搜索条件，添加到SQL语句中
        let conditions = []; // 初始化条件数组
        // 根据前端的请求数据加条件
        if (class_id) {
            conditions.push(`classes.id = ?`);
            params.push(class_id);
        }
        if (exam_id) {
            conditions.push(`exams.id = ?`);
            params.push(exam_id);
        }
        if (program_id) {
            conditions.push(`programs.id = ?`);
            params.push(program_id);
        }
        // 如果有搜索条件，添加WHERE子句
        if (conditions.length > 0) {
            sql += ` WHERE ${conditions.join(' AND ')}`;
        }
        return query(sql, params);
    }
    // 2.出勤率趋势表--Attendance
    static async attend_chart(class_id, program_id, start_time, end_time) {
        let sql = `
            SELECT
                attendance.date,
                SUM(CASE WHEN status = '正常' THEN 1 ELSE 0 END) AS 'normal',
                SUM(CASE WHEN status = '迟到' THEN 1 ELSE 0 END) AS 'late',
                SUM(CASE WHEN status = '旷课' THEN 1 ELSE 0 END) AS 'cul_class',
                SUM(CASE WHEN status = '早退' THEN 1 ELSE 0 END) AS 'leave_early'
            FROM attendance
            JOIN students ON attendance.student_id = students.id 
            JOIN classes ON students.class_id = classes.id 
            JOIN programs ON classes.program_id = programs.id 
        `;
        // 初始化参数数组
        let params = [];

        // 如果有搜索条件，添加到SQL语句中
        let conditions = []; // 初始化条件数组
        // 根据前端的请求数据加条件
        if (class_id !== undefined) {
            conditions.push(`classes.id = ?`);
            params.push(class_id);
        }
        if (program_id !== undefined) {
            conditions.push(`programs.id = ?`);
            params.push(program_id);
        }
        if (start_time !== undefined && end_time !== undefined) {
            conditions.push(`attendance.date BETWEEN ? AND ?`);
            params.push(start_time, end_time);
        }

        // 如果有搜索条件，添加WHERE子句
        if (conditions.length > 0) {
            sql += ` WHERE ${conditions.join(' AND ')}`;
        }

        // 按照日期分组
        sql += ` GROUP BY attendance.date`;
        const result = await query(sql, params);
        return result.map(item => {
            // 转换日期格式
            item.date = new Date(item.date).toLocaleDateString(); // 转换为本地日期格式

            // 计算考勤总数
            const totalAttendance = Number(item.normal) + Number(item.late) + Number(item.cul_class) + Number(item.leave_early);
            console.log('总数：', totalAttendance)
            console.log('of:', item.normal, item.late, item.cul_class, item.leave_early)
            // 计算考勤率百分比
            item.attendanceRate = (item.normal / totalAttendance).toFixed(2) * 100 + "%"; // 保留两位小数

            // 删除不再需要的字段
            delete item.normal;
            delete item.late;
            delete item.cul_class;
            delete item.leave_early;

            return item;
        });
    }
    // 3.学习进度追踪图
    static async progress_chart(class_id, program_id, start_time, end_time) {
        // 1.统计作业的分数 占比：60%
        let homework_sql = `
            SELECT assignment_submissions.score, students.name AS student_name, assignment_submissions.submitted_at 
            FROM assignment_submissions 
            JOIN students ON assignment_submissions.student_id = students.id 
            JOIN classes ON students.class_id = classes.id 
            JOIN programs ON classes.program_id = programs.id 
            WHERE ? IS NULL OR classes.id = ?
            AND ? IS NULL OR programs.id = ?
            AND (? IS NULL OR ? IS NULL OR assignment_submissions.submitted_at BETWEEN ? AND ?)
            GROUP BY students.id, assignment_submissions.submitted_at
        `;
        // 2.统计考试的分数 占比：30%
        let test_sql = `
            SELECT exam_results.score_all AS score, students.name AS student_name, exams.date AS exam_date
            FROM exam_results
            JOIN students ON exam_results.student_id = students.id 
            JOIN classes ON students.class_id = classes.id 
            JOIN programs ON classes.program_id = programs.id 
            JOIN exams ON exam_results.exam_id = exams.id 
            WHERE ? IS NULL OR classes.id = ?
            AND ? IS NULL OR programs.id = ?
            AND (? IS NULL OR ? IS NULL OR exams.date BETWEEN ? AND ?) 
            GROUP BY students.id, exams.date
        `;
        // 3.统计考勤的分数 占比：10%
        let attend_sql = `
            SELECT attendance.score, students.name AS student_name, attendance.date 
            FROM attendance 
            JOIN students ON attendance.student_id = students.id 
            JOIN classes ON students.class_id = classes.id 
            JOIN programs ON classes.program_id = programs.id 
            WHERE ? IS NULL OR classes.id = ?
            AND ? IS NULL OR programs.id = ?
            AND (? IS NULL OR ? IS NULL OR attendance.date BETWEEN ? AND ?) 
            GROUP BY students.id, attendance.date
        `;
        // 4.将上面的分数*占比，算出每个学生对应的最后得分及学习进度的抽象表示
        try {
            const params = [class_id, class_id, program_id, program_id, start_time, end_time, start_time, end_time,
                class_id, class_id, program_id, program_id, start_time, end_time, start_time, end_time,
                class_id, class_id, program_id, program_id, start_time, end_time, start_time, end_time];
            const [homeworkResults, testResults, attendResults] = await Promise.all([
                query(homework_sql, params),
                query(test_sql, params),
                query(attend_sql, params)
            ]);

            const progressData = {};

            const addScore = (progressData, student_name, date, scoreType, score) => {
                const formattedDate = new Date(date).toLocaleDateString('zh-CN', { year: 'numeric', month: '2-digit', day: '2-digit' });
                if (!progressData[student_name]) {
                    progressData[student_name] = {};
                }
                if (!progressData[student_name][formattedDate]) {
                    progressData[student_name][formattedDate] = { date: formattedDate, score: 0, homework: 0, tests: 0, attendance: 0 };
                }
                progressData[student_name][formattedDate][scoreType] += score;
                progressData[student_name][formattedDate].score = progressData[student_name][formattedDate].homework + progressData[student_name][formattedDate].tests + progressData[student_name][formattedDate].attendance;
            };

            homeworkResults.forEach(result => {
                const { score, student_name, submitted_at } = result;
                addScore(progressData, student_name, submitted_at, 'homework', score * 0.6); // 作业分数占比60%
            });

            testResults.forEach(result => {
                const { score, student_name, exam_date } = result;
                addScore(progressData, student_name, exam_date, 'tests', score * 0.3); // 考试分数占比30%
            });

            attendResults.forEach(result => {
                const { score, student_name, date } = result;
                addScore(progressData, student_name, date, 'attendance', score * 0.1); // 考勤分数占比10%
            });

            // 构造最终返回的数据结构
            const finalResults = {};
            Object.keys(progressData).forEach(studentName => {
                finalResults[studentName] = Object.values(progressData[studentName]);
            });

            return finalResults;
        } catch (error) {
            console.error('Error fetching progress data:', error);
            throw error;
        }
    }
    // 4.内务分数排行图
    static async housekeep_chart(class_id, program_id, start_time, end_time) {
        let sql = `
            SELECT inspects.score, inspects.inspect_num
            FROM inspects
            JOIN classes ON inspects.class_id = classes.id 
            JOIN programs ON classes.program_id = programs.id 
        `;
        // 初始化参数数组
        let params = [];

        // 如果有搜索条件，添加到SQL语句中
        let conditions = []; // 初始化条件数组
        // 根据前端的请求数据加条件
        if (class_id !== undefined) {
            conditions.push(`classes.id = ?`);
            params.push(class_id);
        }
        if (program_id !== undefined) {
            conditions.push(`programs.id = ?`);
            params.push(program_id);
        }
        if (start_time !== undefined && end_time !== undefined) {
            conditions.push(`inspects.inspect_date BETWEEN ? AND ?`);
            params.push(start_time, end_time);
        }

        // 如果有搜索条件，添加WHERE子句
        if (conditions.length > 0) {
            sql += ` WHERE ${conditions.join(' AND ')}`;
        }

        // 按照检查编号和日期分组
        sql += ` GROUP BY inspects.inspect_num`;

        // const result = await query(sql, params);
        // return result.map(item => {
        //     // 转换日期格式
        //     item.inspect_date = new Date(item.inspect_date).toLocaleDateString(); // 转换为本地日期格式
        //     return item;
        // })
        return await query(sql, params)
    }
    // 二. 排名榜单
    // 1.成绩排名
    static async classRank_chart(start_time, end_time) {
        // 构建基本的SQL查询语句
        let sql = `
            SELECT exam_results.score_all, students.name AS student_name, exams.date AS exam_date
            FROM exam_results 
            JOIN students ON exam_results.student_id = students.id 
            JOIN exams ON exam_results.exam_id = exams.id
        `;

        // 初始化参数数组
        let params = [];
        // 如果有搜索条件，添加到SQL语句中
        let conditions = []; // 初始化条件数组
        // 检查是否提供了开始时间和结束时间
        if (start_time !== undefined && end_time !== undefined) {
            conditions.push(`exams.date BETWEEN ? AND ?`);
            params.push(start_time, end_time);
        }
        // 如果有搜索条件，添加WHERE子句
        if (conditions.length > 0) {
            sql += ` WHERE ${conditions.join(' AND ')}`;
        }
        // 添加ORDER BY子句以排序分数
        sql += ` ORDER BY exam_results.score_all DESC`;
        try {
            // 执行查询
            const result = await query(sql, params);
            // 处理成前端需要的数据格式
            const processedResult = result.map(item => {
                // 转换日期格式
                item.date = new Date(item.exam_date).toLocaleDateString(); // 转换为本地日期格式
                // 删除不再需要的字段
                delete item.exam_date;
                return item;
            });

            // 获取排名前五和后五
            const topFive = processedResult.slice(0, 5);
            const bottomFive = processedResult.slice(-5);

            // 返回前五名和后五名
            return {
                topFive,
                bottomFive
            };
        } catch (error) {
            console.error('Error fetching class rank data:', error);
            throw error;
        }
    }
    // 2.考勤排名
    static async attendRank_chart(start_time, end_time) {
        let sql = `
        SELECT attendance.score, attendance.date, students.name AS student_name
        FROM attendance 
        JOIN students ON attendance.student_id = students.id
    `;
        // 初始化参数数组
        let params = [];

        // 如果有搜索条件，添加到SQL语句中
        let conditions = [`attendance.state_del = 0`]; // 初始化条件数组

        if (start_time !== undefined && end_time !== undefined) {
            conditions.push(`attendance.date BETWEEN ? AND ?`);
            params.push(start_time, end_time);
        }

        // 如果有搜索条件，添加WHERE子句
        if (conditions.length > 0) {
            sql += ` WHERE ${conditions.join(' AND ')}`;
        }

        // 添加ORDER BY子句以排序分数
        sql += ` ORDER BY attendance.score DESC`;

        const result = await query(sql, params);
        // 处理成前端需要的数据格式
        const processedResult = result.map(item => {
            // 转换日期格式
            item.date = new Date(item.date).toLocaleDateString(); // 转换为本地日期格式

            return item;
        });
        // 获取排名前五和后五
        const topFive = processedResult.slice(0, 5);
        const bottomFive = processedResult.slice(-5);

        return {
            topFive,
            bottomFive
        };
    }
    // 3.内务排名
    static async housekeepRank_chart(start_time, end_time) {
        let sql = `
        SELECT score, inspect_date, inspect_num, classes.name AS class_name
        FROM inspects
        JOIN classes ON inspects.class_id = classes.id
    `;
        // 初始化参数数组
        let params = [];

        // 如果有搜索条件，添加到SQL语句中
        let conditions = [`inspects.state_del = 0`]; // 初始化条件数组

        if (start_time !== undefined && end_time !== undefined) {
            conditions.push(`inspect_date BETWEEN ? AND ?`);
            params.push(start_time, end_time);
        }

        // 如果有搜索条件，添加WHERE子句
        if (conditions.length > 0) {
            sql += ` WHERE ${conditions.join(' AND ')}`;
        }

        // 添加ORDER BY子句以排序分数
        sql += ` ORDER BY score DESC`;

        const result = await query(sql, params);
        // 处理成前端需要的数据格式
        const processedResult = result.map(item => {
            // 转换日期格式
            item.inspect_date = new Date(item.inspect_date).toLocaleDateString(); // 转换为本地日期格式

            return item;
        });
        // 获取排名前五和后五
        const topFive = processedResult.slice(0, 5);
        const bottomFive = processedResult.slice(-5);

        return {
            topFive,
            bottomFive
        };
    }
    // 4.作业分数排名
    static async homeworkRank_chart(start_time, end_time) {
        let sql = `
        SELECT assignment_submissions.score, assignment_submissions.submitted_at, students.name AS student_name
        FROM assignment_submissions
        JOIN students ON assignment_submissions.student_id = students.id
    `;
        // 初始化参数数组
        let params = [];

        // 如果有搜索条件，添加到SQL语句中
        let conditions = []; // 初始化条件数组

        if (start_time !== undefined && end_time !== undefined) {
            conditions.push(`submitted_at BETWEEN ? AND ?`);
            params.push(start_time, end_time);
        }

        // 如果有搜索条件，添加WHERE子句
        if (conditions.length > 0) {
            sql += ` WHERE ${conditions.join(' AND ')}`;
        }

        // 添加ORDER BY子句以排序分数
        sql += ` ORDER BY assignment_submissions.score DESC`;

        const result = await query(sql, params);
        // 处理成前端需要的数据格式
        const processedResult = result.map(item => {
            // 转换日期格式
            item.submitted_at = new Date(item.submitted_at).toLocaleDateString(); // 转换为本地日期格式

            return item;
        });
        // 获取排名前五和后五
        const topFive = processedResult.slice(0, 5);
        const bottomFive = processedResult.slice(-5);

        return {
            topFive,
            bottomFive
        };
    }
    // 三. 学习成绩分析
    // 1.学生折线统计图-所有成绩的对比
    static async allScore_chart(start_time, end_time) {
        let sql = `
            SELECT score_all, score_write, score_practe, score_meet, 
                students.name AS student_name, exams.name AS exam_name, exams.date AS exam_date
            FROM exam_results 
            JOIN students ON exam_results.student_id = students.id
            JOIN exams ON exam_results.exam_id = exams.id
        `;
        // 初始化参数数组
        let params = [];

        // 如果有搜索条件，添加到SQL语句中
        let conditions = []; // 初始化条件数组

        if (start_time !== undefined && end_time !== undefined) {
            conditions.push(`exams.date BETWEEN ? AND ?`);
            params.push(start_time, end_time);
        }

        // 如果有搜索条件，添加WHERE子句
        if (conditions.length > 0) {
            sql += ` WHERE ${conditions.join(' AND ')}`;
        }

        const result = await query(sql, params);
        // 处理成前端需要的数据格式
        return result.map(item => {
            // 转换日期格式
            item.exam_date = new Date(item.exam_date).toLocaleDateString(); // 转换为本地日期格式

            return item;
        });
    }
    // 2.班级平均分趋势表
    static async average_chart(start_time, end_time) {
        // 构建 SQL 查询语句，计算每个班级的平均分
        let sql = `
            SELECT classes.name AS class_name, AVG(exam_results.score_all) AS average_score, classes.id AS class_id, 
            exams.date AS exam_date 
            FROM exam_results 
            JOIN exams ON exam_results.exam_id = exams.id
            JOIN students ON exam_results.student_id = students.id 
            JOIN classes ON students.class_id = classes.id 
        `;

        // 初始化参数数组
        let params = [];

        // 如果有搜索条件，添加到SQL语句中
        if (start_time !== undefined && end_time !== undefined) {
            sql += ` WHERE exams.date BETWEEN ? AND ?`;
            params.push(start_time, end_time);
        }

        sql += ` GROUP BY classes.id, exams.date`;

        // 执行查询
        const result = await query(sql, params);

        // 处理成前端需要的数据格式
        return result.map(item => {
            // 转换日期格式
            item.exam_date = new Date(item.exam_date).toLocaleDateString(); // 转换为本地日期格式
            item.average_score = parseFloat(item.average_score).toFixed(1); // 转换为1位小数的字符串
            return item;
        });
    }
    // 3.学生成绩明细表格
    static async student_chart(start_time, end_time) {
        // 构建 SQL 查询语句，计算每个学生的总分和平均分
        let sql = `
            SELECT students.name AS student_name, classes.name AS class_name, 
                AVG(exam_results.score_all) AS average_score, 
                (exam_results.score_write + exam_results.score_practe + exam_results.score_meet + exam_results.score_project) AS total_score
            FROM exam_results 
            JOIN exams ON exam_results.exam_id = exams.id
            JOIN students ON exam_results.student_id = students.id 
            JOIN classes ON students.class_id = classes.id 
        `;
    
        // 初始化参数数组
        let params = [];
    
        // 如果有搜索条件，添加到SQL语句中
        if (start_time !== undefined && end_time !== undefined) {
            sql += ` WHERE exams.date BETWEEN ? AND ?`;
            params.push(start_time, end_time);
        }
    
        // 添加 GROUP BY 子句
        sql += ` GROUP BY students.name, classes.name, exams.date`;
    
        // 添加 ORDER BY 子句，按照 total_score 降序排序
        sql += ` ORDER BY total_score DESC`;
    
        // 执行查询
        const result = await query(sql, params);
    
        // 处理成前端需要的数据格式
        return result.map((item, index) => {
            // 转换平均分为1位小数的字符串
            item.average_score = parseFloat(item.average_score).toFixed(1);
    
            // 添加排名，即数组的下标 + 1（因为排名通常是从1开始的）
            item.rank = index + 1;
    
            return item;
        });
    }
    // 4.考勤详情表
    static async attenddetail_chart(){
        let sql = `
            SELECT
                SUM(CASE WHEN status = '正常' THEN 1 ELSE 0 END) AS '正常',
                SUM(CASE WHEN status = '迟到' THEN 1 ELSE 0 END) AS '迟到',
                SUM(CASE WHEN status = '旷课' THEN 1 ELSE 0 END) AS '旷课',
                SUM(CASE WHEN status = '早退' THEN 1 ELSE 0 END) AS '早退'
            FROM attendance
            JOIN students ON attendance.student_id = students.id 
            JOIN classes ON students.class_id = classes.id 
            JOIN programs ON classes.program_id = programs.id 
        `
        return query(sql)
    }
    // 5.每个作业的完成率
    static async workcom_chart(){
        let sql = `
            SELECT 
                a.id,
                a.title AS assignment_name,
                COUNT(asub.student_id) AS total_submissions,
                SUM(asub.status IN ('完成', '迟交')) AS completed_count,
                ROUND(
                    SUM(asub.status IN ('完成', '迟交')) * 100.0 / 
                    NULLIF(COUNT(asub.student_id), 0),
                    2
                ) AS completion_rate
            FROM 
                assignments a
            LEFT JOIN 
                assignment_submissions asub ON a.id = asub.assignment_id
            GROUP BY 
                a.id, a.title
            ORDER BY 
                completion_rate DESC;
        `
        return query(sql)
    }
    // 6.作业的完成数量统计
    static async workcom_number(){
        let sql = `
            SELECT 
                SUM(CASE WHEN status = '完成' THEN 1 ELSE 0 END) AS completed_count,
                SUM(CASE WHEN status = '未完成' THEN 1 ELSE 0 END) AS incomplete_count,
                SUM(CASE WHEN status = '迟交' THEN 1 ELSE 0 END) AS late_count,
                COUNT(*) AS total_count
            FROM assignment_submissions
        `
        return query(sql)
    }
}
module.exports = AnalysisModel